﻿using System.Data;
using System.Web.Mvc;

namespace MvcApplication7.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {

            return View();
        }

        [HttpPost]
        public ActionResult Index(FormCollection frmCollection)
        {
            // Get the DataTable.
            DataTable table = GetTable();

            //get new data table 
            DataTable newTable = GetNewTable();

            for (int i = table.Rows.Count - 1; i > -1; i--)
            {
                var gTotalIndex = (table.Rows.Count - i) - 1;
                newTable.Rows.Add(i, null, null, table.Rows[gTotalIndex]["Category"], table.Rows[gTotalIndex]["MOR_Group"], table.Rows[gTotalIndex]["Record_Id"], table.Rows[gTotalIndex]["Cost_Type"], table.Rows[gTotalIndex]["Exp_Cost"]);
            }

            var oTotal = 0;
            for (int i = newTable.Rows.Count - 1; i > -1; i--)
            {
                if (newTable.Rows[i]["Category"].ToString() == "GRAND_TOTAL")
                    continue;
                if (newTable.Rows[i]["Category"].ToString() == "GRP_TOTAL")
                {
                    oTotal = 0;
                    newTable.Rows[i]["Group_Total_Index"] = oTotal;
                    oTotal++;
                }
                else
                {
                    newTable.Rows[i]["Group_Total_Index"] = oTotal;
                    oTotal++;
                }
            }

            var pTotal = 0;
            for (int i = newTable.Rows.Count - 1; i > -1; i--)
            {
                if (newTable.Rows[i]["Category"].ToString() == "GRAND_TOTAL" || newTable.Rows[i]["Category"].ToString() == "GRP_TOTAL")
                    continue;
                if (newTable.Rows[i]["Category"].ToString() == "PROJ_TOTAL")
                {
                    pTotal = 0;
                    newTable.Rows[i]["Project_Total_Index"] = pTotal;
                    pTotal++;
                }
                else
                {
                    newTable.Rows[i]["Project_Total_Index"] = pTotal;
                    pTotal++;
                }
            }


            var xmlDoc = GetXMLDoc(newTable);
            Response.ClearContent();
            Response.AddHeader("content-disposition", "attachment; filename=book.xls");
            Response.ContentType = "application/excel";

            //Pass rendered string to Response object which would be presented to user for download
            Response.Write(xmlDoc.ToString());
            Response.End();

            return Content("true");
        }

        private static object GetXMLDoc(DataTable newTable)
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("<?xml version=\"1.0\"?>\n");
            sb.Append("<?mso-application progid=\"Excel.Sheet\"?>\n");
            sb.Append(
              "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
            sb.Append("xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
            sb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
            sb.Append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
            sb.Append("xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n");

            sb.Append("<Styles>\n");
            sb.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\n");
            sb.Append("<Alignment ss:Vertical=\"Bottom\"/>\n");
            sb.Append("<Borders/>\n");
            sb.Append("<Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\"/>\n");
            sb.Append("<Interior/>\n");
            sb.Append("<NumberFormat/>\n");
            sb.Append("<Protection/>\n");
            sb.Append("</Style>\n");
            sb.Append("</Styles>\n");
            sb.Append("<Worksheet ss:Name=\"Sheet1\">\n");

            sb.Append("<Table>\n");

            sb.Append("<Column ss:Width=\"58.5\"/>\n");
            sb.Append("<Column ss:Width=\"57.75\"/>\n");
            sb.Append("<Row ss:AutoFitHeight=\"0\"/>\n");
            sb.Append("<Row ss:Index=\"3\" ss:AutoFitHeight=\"0\">\n");
            sb.Append("<Cell><Data ss:Type=\"String\">MOR Group</Data></Cell>\n");
            sb.Append("<Cell><Data ss:Type=\"String\">Record Id</Data></Cell>\n");
            sb.Append("<Cell><Data ss:Type=\"String\">Cost Type</Data></Cell>\n");
            sb.Append("<Cell><Data ss:Type=\"String\">Exp Cost</Data></Cell>\n");
            sb.Append("</Row>\n");


            for (int i = 0; i < newTable.Rows.Count; i++)
            {
                if (newTable.Rows[i]["Category"].ToString().ToUpper() == "NEW_PROJ" &&
                    newTable.Rows[i]["MOR_Group"].ToString() != "")
                {
                    sb.Append("<Row ss:AutoFitHeight=\"0\">\n");
                    sb.Append("<Cell><Data ss:Type=\"String\">" + newTable.Rows[i]["MOR_Group"] + "</Data></Cell>\n");
                    sb.Append("<Cell><Data ss:Type=\"Number\">" + newTable.Rows[i]["Record_Id"] + "</Data></Cell>\n");
                    sb.Append("<Cell><Data ss:Type=\"String\">" + newTable.Rows[i]["Cost_Type"] + "</Data></Cell>\n");
                    sb.Append("<Cell><Data ss:Type=\"Number\">" + newTable.Rows[i]["Exp_Cost"] + "</Data></Cell>\n");
                    sb.Append("</Row>\n");
                }
                else if (newTable.Rows[i]["Category"].ToString().ToUpper() == "NEW_PROJ" &&
                     newTable.Rows[i]["MOR_Group"].ToString() == string.Empty)
                {
                    sb.Append("<Row ss:AutoFitHeight=\"0\">\n");
                    sb.Append("<Cell ss:Index=\"2\"><Data ss:Type=\"Number\">" + newTable.Rows[i]["Record_Id"] +
                              "</Data></Cell>\n");
                    sb.Append("<Cell><Data ss:Type=\"String\">" + newTable.Rows[i]["Cost_Type"] + "</Data></Cell>\n");
                    sb.Append("<Cell><Data ss:Type=\"Number\">" + newTable.Rows[i]["Exp_Cost"] + "</Data></Cell>\n");
                    sb.Append("</Row>\n");
                }
                else switch (newTable.Rows[i]["Category"].ToString().ToUpper())
                {
                    case "SAME_PROJ":
                        sb.Append("<Row ss:AutoFitHeight=\"0\">\n");
                        sb.Append("<Cell ss:Index=\"3\"><Data ss:Type=\"String\">" + newTable.Rows[i]["Cost_Type"] +
                                  "</Data></Cell>\n");
                        sb.Append("<Cell><Data ss:Type=\"Number\">" + newTable.Rows[i]["Exp_Cost"] + "</Data></Cell>\n");
                        sb.Append("</Row>\n");
                        break;
                    case "PROJ_TOTAL":
                        sb.Append("<Row ss:AutoFitHeight=\"0\">\n");
                        sb.Append("<Cell ss:Index=\"2\"><Data ss:Type=\"String\">" + newTable.Rows[i]["Record_Id"] +
                                  "</Data></Cell>\n");
                        sb.Append(
                            "<Cell ss:Index=\"4\" ss:Formula=\"=SUM(R[-5]C:R[-1]C)\"><Data ss:Type=\"Number\"></Data></Cell>\n");
                        sb.Append("</Row>\n");
                        break;
                    case "GRP_TOTAL":
                        sb.Append("<Row ss:AutoFitHeight=\"0\">\n");
                        sb.Append("<Cell><Data ss:Type=\"String\">" + newTable.Rows[i]["MOR_Group"] + "</Data></Cell>\n");
                        sb.Append(
                            "<Cell ss:Index=\"4\" ss:Formula=\"=SUM(R[-12]C:R[-8]C)+SUM(R[-6]C:R[-2]C)\"><Data ss:Type=\"Number\"></Data></Cell>\n");
                        sb.Append("</Row>\n");
                        break;
                    case "GRAND_TOTAL":
                        string grandTotalFormula = string.Empty;
                        int? last_new_proj = null;
                        for (int index = newTable.Rows.Count - 1; index > 0; --index)
                        {
                            if (newTable.Rows[index].Field<string>("Category") != "SAME_PROJ") continue;
                            for (int j = (last_new_proj ?? index) - 1; j >= 0; --j)
                            {
                                if (newTable.Rows[j].Field<string>("Category") != "NEW_PROJ") continue;
                                grandTotalFormula += string.Format("SUM(R[-{0}]C:R[-{1}]C)+",
                                                     newTable.Rows[index].Field<int>("Grand_Total_Index"),
                                                     newTable.Rows[j].Field<int>("Grand_Total_Index"));
                                last_new_proj = j;
                                index = j;
                                break;
                            }
                        }
                        sb.Append("<Row ss:AutoFitHeight=\"0\">\n");
                        sb.Append("<Cell><Data ss:Type=\"String\">Grand Total</Data></Cell>\n");
                        sb.Append(
                            "<Cell ss:Index=\"4\" ss:Formula=\"=" + grandTotalFormula.TrimEnd('+') + "\"><Data ss:Type=\"Number\"></Data></Cell>\n");
                        sb.Append("</Row>\n");
                        break;
                }
            }

            sb.Append("</Table>\n");

            sb.Append("</Worksheet>\n");

            sb.Append("</Workbook>\n");

            return sb.ToString();
        }

        static DataTable GetNewTable()
        {
            DataTable table = new DataTable();
            table.Columns.Add("Grand_Total_Index", typeof(int));
            table.Columns.Add("Group_Total_Index", typeof(int));
            table.Columns.Add("Project_Total_Index", typeof(int));
            table.Columns.Add("Category", typeof(string));
            table.Columns.Add("MOR_Group", typeof(string));
            table.Columns.Add("Record_Id", typeof(string));
            table.Columns.Add("Cost_Type", typeof(string));
            table.Columns.Add("Exp_Cost", typeof(decimal));
            return table;
        }

        static DataTable GetTable()
        {
            DataTable table = new DataTable();
            table.Columns.Add("Category", typeof(string));
            table.Columns.Add("MOR_Group", typeof(string));
            table.Columns.Add("Record_Id", typeof(string));
            table.Columns.Add("Cost_Type", typeof(string));
            table.Columns.Add("Exp_Cost", typeof(decimal));

            table.Rows.Add("NEW_PROJ", "ABC", "1001", "HW", 10);
            table.Rows.Add("SAME_PROJ", "", "", "SW", 20);
            table.Rows.Add("SAME_PROJ", "", "", "OPS", 20);
            table.Rows.Add("SAME_PROJ", "", "", "MAINT", 20);
            table.Rows.Add("SAME_PROJ", "", "", "LABOR", 20);
            table.Rows.Add("PROJ_TOTAL", "", "1001 Total", "", null);
            //
            table.Rows.Add("NEW_PROJ", "", "1002", "HW", 40);
            table.Rows.Add("SAME_PROJ", "", "", "SW", 20);
            table.Rows.Add("SAME_PROJ", "", "", "OPS", 20);
            table.Rows.Add("SAME_PROJ", "", "", "MAINT", 20);
            table.Rows.Add("SAME_PROJ", "", "", "LABOR", 20);
            table.Rows.Add("PROJ_TOTAL", "", "1002 Total", "", null);
            //
            table.Rows.Add("GRP_TOTAL", "ABC Total", "", "", null);

            table.Rows.Add("NEW_PROJ", "PQR", "1003", "HW", 10);
            table.Rows.Add("SAME_PROJ", "", "", "SW", 20);
            table.Rows.Add("SAME_PROJ", "", "", "OPS", 20);
            table.Rows.Add("SAME_PROJ", "", "", "MAINT", 20);
            table.Rows.Add("SAME_PROJ", "", "", "LABOR", 20);
            table.Rows.Add("PROJ_TOTAL", "", "1003 Total", "", null);
            //
            table.Rows.Add("GRP_TOTAL", "PQR Total", "", "", null);

            table.Rows.Add("NEW_PROJ", "XYZ", "1004", "HW", 10);
            table.Rows.Add("SAME_PROJ", "", "", "SW", 20);
            table.Rows.Add("SAME_PROJ", "", "", "OPS", 20);
            table.Rows.Add("SAME_PROJ", "", "", "MAINT", 20);
            table.Rows.Add("SAME_PROJ", "", "", "LABOR", 20);
            table.Rows.Add("PROJ_TOTAL", "", "1004 Total", "", null);
            //
            table.Rows.Add("GRP_TOTAL", "XYZ Total", "", "", null);


            table.Rows.Add("GRAND_TOTAL", "", "", "", null);

            return table;
        }
    }
}
